\documentclass{article}
\usepackage{graphicx}
\setlength{\textwidth}{6.5in}
%% \setlength{\textheight}{9in} %% Comment again when submitting to GS.
%% %% %\setlength{\parindent}{10mm}
%%  \setlength{\parskip}{0.5\baselineskip}
\setlength{\oddsidemargin}{-0.5in}
\setlength{\evensidemargin}{-0.5in}
%%  \setlength{\topmargin}{-0.45in}
%%  \raggedbottom
\begin{document}

\title{Optimizing database queries for a bioinformatics problem}
\author{Faheem Mitha}
\maketitle

\section{Introduction}

% http://wiki.postgresql.org/wiki/SlowQueryQuestions

I have a data management project that uses a database for storing
data. I talked to \#postgresql on freenode on several occasions. and
they asked for more information, so I compiled this docuument to
answer (hopefully) most of those questions. See Sections below for
more info.

\begin{itemize}
\item Section~\ref{hwinfo}: Hardware and OS info
\item Section~\ref{pgconfig}: PostgreSQL config info
\item Section~\ref{dbinfo}: Specific details about database: schema etc.
\end{itemize}

\section{Hardware and OS info}\label{hwinfo}
\begin{itemize}
\item Amount of memory: 64 GB (sixteen 4 GB modules) of RAM                  
\item Number and type of CPUS: 4XQuad-Core AMD Opteron(tm) Processor 8384 (16cores)
\begin{verbatim}
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 16
model           : 4
model name      : Quad-Core AMD Opteron(tm) Processor 8384
stepping        : 2
cpu MHz         : 2700.117
cache size      : 512 KB
physical id     : 0
siblings        : 4
core id         : 0
cpu cores       : 4
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm 3dnowext 3dnow constant_tsc rep_good pni monitor cx16 popcnt lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs skinit wdt
bogomips        : 5404.42
TLB size        : 1024 4K pages
clflush size    : 64
cache_alignment : 64
address sizes   : 48 bits physical, 48 bits virtual
power management: ts ttp tm stc 100mhzsteps hwpstate
\end{verbatim}

\item Hard disk description - size and number: x1TB Seagate SAS ES.2
  drives (ST31000640SS) connected to a 3ware 9690SA-8I card in RAID 10
  (more info shown below)

\begin{verbatim}
Information about the RAID:

/c0 Driver Version = 2.26.02.010
/c0 Model = 9690SA-8I
/c0 Available Memory = 448MB
/c0 Firmware Version = FH9X 4.06.00.004
/c0 Bios Version = BE9X 4.05.00.015
/c0 Boot Loader Version = BL9X 3.08.00.001
/c0 Serial Number = L340504A9020298
/c0 PCB Version = Rev 041
/c0 PCHIP Version = 2.00
/c0 ACHIP Version = 1501291E
/c0 Controller Phys = 8
/c0 Connections = 6 of 128
/c0 Drives = 6 of 128
/c0 Units = 1 of 128
/c0 Active Drives = 6 of 128
/c0 Active Units = 1 of 32
/c0 Max Drives Per Unit = 32
/c0 Total Optimal Units = 1
/c0 Not Optimal Units = 0
/c0 Disk Spinup Policy = 1
/c0 Spinup Stagger Time Policy (sec) = 1
/c0 Auto-Carving Policy = off
/c0 Auto-Carving Size = 2048 GB
/c0 Auto-Rebuild Policy = on
/c0 Rebuild Rate = 1
/c0 Verify Rate = 5
/c0 Controller Bus Type = PCIe
/c0 Controller Bus Width = 4 lanes
/c0 Controller Bus Speed = 2.5 Gbps/lane

Unit  UnitType  Status         %RCmpl  %V/I/M  Stripe  Size(GB)  Cache
AVrfy
------------------------------------------------------------------------------
u0    RAID-10   OK             -       -       64K     2793.94   ON OFF

VPort Status         Unit Size      Type  Phy Encl-Slot    Model
------------------------------------------------------------------------------
p0    OK             u0   931.51 GB SAS   0   -            SEAGATE
ST31000640SS
p1    OK             u0   931.51 GB SAS   1   -            SEAGATE
ST31000640SS
p2    OK             u0   931.51 GB SAS   2   -            SEAGATE
ST31000640SS
p3    OK             u0   931.51 GB SAS   3   -            SEAGATE
ST31000640SS
p4    OK             u0   931.51 GB SAS   4   -            SEAGATE
ST31000640SS
p5    OK             u0   931.51 GB SAS   5   -            SEAGATE
ST31000640SS

Name  OnlineState  BBUReady  Status    Volt     Temp     Hours LastCapTest
---------------------------------------------------------------------------
bbu   On           Yes       OK        OK       OK       182 09-May-2009

Information about the drives:

/c0/p0 Status = OK
/c0/p0 Model = SEAGATE ST31000640SS
/c0/p0 Firmware Version = 0001
/c0/p0 Serial = 9QJ2NNLT
/c0/p0 Capacity = 931.51 GB (1953525167 Blocks)
/c0/p0 WWN = 5000c5000d32c544
/c0/p0 Drive Type = SAS
/c0/p0 Interface Type = Direct
/c0/p0 Drive Ports = 2
/c0/p0 Drive Connections = 1
/c0/p0 Reallocated Sectors = 108
/c0/p0 Power On Hours = 2027
/c0/p0 Temperature = 15 deg C
/c0/p0 Spindle Speed = 7200 RPM
/c0/p0 Link Speed Supported = 1.5 Gbps and 3.0 Gbps
/c0/p0 Link Speed = 3.0 Gbps
/c0/p0 Queuing Supported = Yes
/c0/p0 Queuing Enabled = Yes
/c0/p0 Identify Status = N/A
/c0/p0 Belongs to Unit = u0
\end{verbatim}


\makeatletter  % makes '@' an ordinary character
\renewcommand{\verbatim@font}{%
  \ttfamily\small\catcode`\<=\active\catcode`\>=\active%
}
\makeatother   % makes '@' a special symbol again

\item Hard drive speeds:
\begin{verbatim}
11:04 < BlueAidan\_work> have you benchmarked the disks alone to make sure 
everything is working fine outside of pg?
11:05 < BlueAidan\_work> faheem: this is a good general guide: 
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm

$ sudo dd if=/dev/zero of=big-testfile bs=1024k count=250
262144000 bytes (262 MB) copied, 0.355373 s, 738 MB/s
262144000 bytes (262 MB) copied, 0.353948 s, 741 MB/s
262144000 bytes (262 MB) copied, 0.393152 s, 667 MB/s
$ sudo dd if=/dev/zero of=big-testfile bs=1024k count=500
524288000 bytes (524 MB) copied, 0.751423 s, 698 MB/s
524288000 bytes (524 MB) copied, 0.701279 s, 748 MB/s
524288000 bytes (524 MB) copied, 0.751901 s, 697 MB/s
$ sudo dd if=/dev/zero of=big-testfile bs=1024k count=1000
1048576000 bytes (1.0 GB) copied, 1.42567 s, 735 MB/s
1048576000 bytes (1.0 GB) copied, 1.42874 s, 734 MB/s
1048576000 bytes (1.0 GB) copied, 1.46446 s, 716 MB/s
\end{verbatim}

From http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm
\begin{verbatim}
$ time sh -c ``dd if=/dev/zero of=bigfile bs=8k count=16000000 && sync''
16000000+0 records in
16000000+0 records out
131072000000 bytes (131 GB) copied, 3135.03 s, 41.8 MB/s

real    59m10.214s
user    0m2.300s
sys     4m43.658s

$ time dd if=bigfile of=/dev/null bs=8k
16000000+0 records in
16000000+0 records out
131072000000 bytes (131 GB) copied, 465.976 s, 281 MB/s

real    7m45.986s
user    0m1.628s
sys     1m51.163s
\end{verbatim}

\begin{verbatim}
$sudo bonnie++ -u 0:0
Using uid:0, gid:0.
Writing with putc()...done
Writing intelligently...done
Rewriting...done
Reading with getc()...done
Reading intelligently...done
start 'em...done...done...done...
Create files in sequential order...done.
Stat files in sequential order...done.
Delete files in sequential order...done.
Create files in random order...done.
Stat files in random order...done.
Delete files in random order...done.
Version 1.03d       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
avicenna    129128M 30435  45 21895   4 22145   4 59891  81 294725  21 648.0   1
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++
avicenna,129128M,30435,45,21895,4,22145,4,59891,81,294725,21,648.0,1,16,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++,+++++,+++
\end{verbatim}

\item 
\begin{verbatim}
11:06 < peerce> iostat -x 5
11:07 < peerce> let that run for 15 to 20 seconds, and look at anything but the 
first sample

Two samples taken some time apart:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.14   23.76    0.00   76.10

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00  1334.00    0.00  429.00     0.00 13628.80    31.77   141.48  336.05   2.33 100.00
cciss/c0d0p1      0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p2      0.00  1334.00    0.00  429.00     0.00 13628.80    31.77   141.48  336.05   2.33 100.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     1.00    0.00   0.00 100.00
dm-1              0.00     0.00    0.00 1776.40     0.00 14211.20     8.00   580.37  329.47   0.56 100.00
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-4              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-5              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-6              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.91    0.00    1.08   29.09    0.00   68.92

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.00  2521.00    0.00  359.40     0.00 22888.00    63.68   142.31  361.24   2.78 100.00
cciss/c0d0p1      0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
cciss/c0d0p2      0.00  2521.00    0.00  359.40     0.00 22888.00    63.68   142.31  361.24   2.78 100.00
dm-0              0.00     0.00    0.00    0.00     0.00     0.00     0.00     1.00    0.00   0.00 100.00
dm-1              0.00     0.00    0.00 2892.80     0.00 23142.40     8.00  1524.37  509.32   0.35 100.00
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-3              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-4              0.00     0.00    0.40    0.00    51.20     0.00   128.00     0.27    0.00 336.00  13.44
dm-5              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
dm-6              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

\end{verbatim}

% Use a medium font for the verbatim environment
\makeatletter  % makes '@' an ordinary character
\renewcommand{\verbatim@font}{%
  \ttfamily\catcode`\<=\active\catcode`\>=\active%
}
\makeatother   % makes '@' a special symbol again

\item Running 64 bit Debian lenny with postgresql 8.4. Package info follows.

\begin{verbatim}
dpkg -l | grep postgres | grep 8.4
ii  postgresql-8.4                      8.4.2-1~bpo50+1                object-relational SQL database, version 8.4
ii  postgresql-8.4-plr                  1:8.3.0.8-2                    Procedural language interface between Postgr
ii  postgresql-client-8.4               8.4.2-1~bpo50+1                front-end programs for PostgreSQL 8.4
ii  postgresql-contrib-8.4              8.4.2-1~bpo50+1                additional facilities for PostgreSQL
ii  postgresql-plpython-8.4             8.4.2-1~bpo50+1                PL/Python procedural language for PostgreSQL
\end{verbatim}
\end{itemize}

\section{PostgreSQL config info}\label{pgconfig}

\begin{verbatim}
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                                                   h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/8.4/main'		# use data in another directory
					# (change requires restart)
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'	# ident configuration file
					# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/8.4-main.pid'		# write an extra PID file
					# (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost', '*' = all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 100			# (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3	# (change requires restart)
unix_socket_directory = '/var/run/postgresql'		# (change requires restart)
#unix_socket_group = ''			# (change requires restart)
#unix_socket_permissions = 0777		# begin with 0 to use octal notation
					# (change requires restart)
#bonjour_name = ''			# defaults to the computer name
					# (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min		# 1s-600s
#ssl = off				# (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
					# (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres'		# (Kerberos only)
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0		# TCP_KEEPIDLE, in seconds;
					# 0 selects the system default
#tcp_keepalives_interval = 0		# TCP_KEEPINTVL, in seconds;
					# 0 selects the system default
#tcp_keepalives_count = 0		# TCP_KEEPCNT;
					# 0 selects the system default


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 2GB			# min 128kB
					# (change requires restart)
#temp_buffers = 8MB			# min 800kB
#max_prepared_transactions = 0		# zero disables the feature
					# (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 1GB				# min 64kB
maintenance_work_mem = 8GB		# min 1MB
#max_stack_depth = 2MB			# min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000		# min 25
					# (change requires restart)
#shared_preload_libraries = ''		# (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0ms		# 0-100 milliseconds
#vacuum_cost_page_hit = 1		# 0-10000 credits
#vacuum_cost_page_miss = 10		# 0-10000 credits
#vacuum_cost_page_dirty = 20		# 0-10000 credits
#vacuum_cost_limit = 200		# 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms			# 10-10000ms between rounds
#bgwriter_lru_maxpages = 100		# 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0		# 0-10.0 multipler on buffers scanned/round

# - Asynchronous Behavior -

#effective_io_concurrency = 1		# 1-1000. 0 disables prefetching


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#fsync = on				# turns forced synchronization on or off
#synchronous_commit = on		# immediate fsync at commit
#wal_sync_method = fsync		# the default is the first option 
					# supported by the operating system:
					#   open_datasync
					#   fdatasync
					#   fsync
					#   fsync_writethrough
					#   open_sync
#full_page_writes = on			# recover from partial page writes
wal_buffers = 16MB			# min 32kB
					# (change requires restart)
#wal_writer_delay = 200ms		# 1-10000 milliseconds

#commit_delay = 0			# range 0-100000, in microseconds
#commit_siblings = 5			# range 1-1000

# - Checkpoints -

checkpoint_segments = 50		# in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min		# range 30s-1h
#checkpoint_completion_target = 0.5	# checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s		# 0 disables

# - Archiving -

#archive_mode = off		# allows archiving to be done
				# (change requires restart)
#archive_command = ''		# command to use to archive a logfile segment
#archive_timeout = 0		# force a logfile segment switch after this
				# number of seconds; 0 disables


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0			# measured on an arbitrary scale
#random_page_cost = 4.0			# same scale as above
#cpu_tuple_cost = 0.01			# same scale as above
#cpu_index_tuple_cost = 0.005		# same scale as above
#cpu_operator_cost = 0.0025		# same scale as above
effective_cache_size = 50GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5			# range 1-10
#geqo_pool_size = 0			# selects default based on effort
#geqo_generations = 0			# selects default based on effort
#geqo_selection_bias = 2.0		# range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 100	# range 1-10000
#constraint_exclusion = partition	# on, off, or partition
#cursor_tuple_fraction = 0.1		# range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8		# 1 disables collapsing of explicit 
					# JOIN clauses


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'		# Valid values are combinations of
					# stderr, csvlog, syslog and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'pg_log'		# directory where log files are written,
					# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# log file name pattern,
					# can include strftime() escapes
#log_truncate_on_rotation = off		# If on, an existing log file of the
					# same name as the new log file will be
					# truncated rather than appended to.
					# But such truncation only occurs on
					# time-driven rotation, not on restarts
					# or size-driven rotation.  Default is
					# off, meaning append to existing files
					# in all cases.
#log_rotation_age = 1d			# Automatic rotation of logfiles will
					# happen after that time.  0 disables.
#log_rotation_size = 10MB		# Automatic rotation of logfiles will 
					# happen after that much log output.
					# 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


# - When to Log -

#client_min_messages = notice		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error

#log_min_messages = warning		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic

#log_error_verbosity = default		# terse, default, or verbose messages

#log_min_error_statement = error	# values in order of decreasing detail:
				 	#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
				 	#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic (effectively off)

#log_min_duration_statement = -1	# -1 is disabled, 0 logs all statements
					# and their durations, > 0 logs only
					# statements running at least this number
					# of milliseconds

#silent_mode = off			# DO NOT USE without syslog or
					# logging_collector
					# (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
log_line_prefix = '%t '			# special values:
					#   %u = user name
					#   %d = database name
					#   %r = remote host and port
					#   %h = remote host
					#   %p = process ID
					#   %t = timestamp without milliseconds
					#   %m = timestamp with milliseconds
					#   %i = command tag
					#   %c = session ID
					#   %l = session line number
					#   %s = session start timestamp
					#   %v = virtual transaction ID
					#   %x = transaction ID (0 if none)
					#   %q = stop here in non-session
					#        processes
					#   %% = '%'
					# e.g. '<%u%%%d> '
#log_lock_waits = off			# log lock waits >= deadlock_timeout
#log_statement = 'none'			# none, ddl, mod, all
#log_temp_files = -1			# log temporary files equal or larger
					# than the specified size in kilobytes;
					# -1 disables, 0 logs all temp files
#log_timezone = unknown			# actually, defaults to TZ environment
					# setting


#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_functions = none			# none, pl, all
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

#autovacuum = on			# Enable autovacuum subprocess?  'on' 
					# requires track_counts to also be on.
#log_autovacuum_min_duration = -1	# -1 disables, 0 logs all actions and
					# their durations, > 0 logs only
					# actions running at least this number
					# of milliseconds.
#autovacuum_max_workers = 3		# max number of autovacuum subprocesses
#autovacuum_naptime = 1min		# time between autovacuum runs
#autovacuum_vacuum_threshold = 50	# min number of row updates before
					# vacuum
#autovacuum_analyze_threshold = 50	# min number of row updates before 
					# analyze
#autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1	# fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
					# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms	# default vacuum cost delay for
					# autovacuum, in milliseconds;
					# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
					# autovacuum, -1 means use
					# vacuum_cost_limit


#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public'		# schema names
#default_tablespace = ''		# a tablespace name, '' uses the default
#temp_tablespaces = ''			# a list of tablespace names, '' uses
					# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#session_replication_role = 'origin'
#statement_timeout = 0			# in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#xmlbinary = 'base64'
#xmloption = 'content'

# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = unknown			# actually, defaults to TZ environment
					# setting
#timezone_abbreviations = 'Default'     # Select the set of available time zone
					# abbreviations.  Currently, there are
					#   Default
					#   Australia
					#   India
					# You can create your own file in
					# share/timezonesets/.
#extra_float_digits = 0			# min -15, max 2
#client_encoding = sql_ascii		# actually, defaults to database
					# encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8'			# locale for system error message
					# strings
lc_monetary = 'en_US.UTF-8'			# locale for monetary formatting
lc_numeric = 'en_US.UTF-8'			# locale for number formatting
lc_time = 'en_US.UTF-8'				# locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.english'

# - Other Defaults -

#dynamic_library_path = '$libdir'
#local_preload_libraries = ''


#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64		# min 10
					# (change requires restart)
# Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.


#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#add_missing_from = off
#array_nulls = on
#backslash_quote = safe_encoding	# on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#regex_flavor = advanced		# advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = ''		# list of custom variable class names
\end{verbatim}

\section{Specific details about database: schema etc.}\label{dbinfo}

\begin{itemize}
\item Plot of schema generated by postgresql-autodoc.

\begin{figure}[tph]
  \begin{center}
    %\includegraphics[width=\textwidth]{schema.pdf}
    %\centerline{\includegraphics{layout.pdf}}
    \scalebox{0.4}{\includegraphics{schema.pdf}}
    %\resizebox{14cm}{6cm}{\includegraphics{schema.eps}}
  \caption{Memory plot of Version 2 of PED query}
  \label{schema}
  \end{center}
\end{figure}

\item Dump of all tables in 'hapmap' schema.
\begin{verbatim}
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: hapmap; Type: SCHEMA; Schema: -; Owner: snp
--

CREATE SCHEMA hapmap;


ALTER SCHEMA hapmap OWNER TO snp;

SET search_path = hapmap, pg_catalog;

--
-- Name: drop_constraint_if_exists(text, text, text); Type: FUNCTION; Schema: hapmap; Owner: snp
--

CREATE FUNCTION drop_constraint_if_exists(t text, k text, s text DEFAULT NULL::text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
IF s IS NOT NULL
THEN
EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k);
ELSE
EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k);
END IF;
EXCEPTION WHEN undefined_object THEN
END;
$$;


ALTER FUNCTION hapmap.drop_constraint_if_exists(t text, k text, s text) OWNER TO snp;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: allele; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE allele (
    val character varying(1) NOT NULL
);


ALTER TABLE hapmap.allele OWNER TO snp;

--
-- Name: anno; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE anno (
    id integer NOT NULL,
    fid character varying(35) NOT NULL,
    rsid character varying(20) NOT NULL,
    chromosome integer NOT NULL,
    location integer NOT NULL,
    allelea_id character varying(1) NOT NULL,
    alleleb_id character varying(1) NOT NULL
);


ALTER TABLE hapmap.anno OWNER TO snp;

--
-- Name: anno_id_seq; Type: SEQUENCE; Schema: hapmap; Owner: snp
--

CREATE SEQUENCE anno_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE hapmap.anno_id_seq OWNER TO snp;

--
-- Name: anno_id_seq; Type: SEQUENCE OWNED BY; Schema: hapmap; Owner: snp
--

ALTER SEQUENCE anno_id_seq OWNED BY anno.id;


--
-- Name: chromo; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE chromo (
    id integer NOT NULL,
    name character varying(2) NOT NULL
);


ALTER TABLE hapmap.chromo OWNER TO snp;

--
-- Name: chromo_id_seq; Type: SEQUENCE; Schema: hapmap; Owner: snp
--

CREATE SEQUENCE chromo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE hapmap.chromo_id_seq OWNER TO snp;

--
-- Name: chromo_id_seq; Type: SEQUENCE OWNED BY; Schema: hapmap; Owner: snp
--

ALTER SEQUENCE chromo_id_seq OWNED BY chromo.id;


--
-- Name: geno; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE geno (
    idlink_id integer NOT NULL,
    anno_id integer NOT NULL,
    snpval_id integer NOT NULL
)
WITH (autovacuum_enabled=true);


ALTER TABLE hapmap.geno OWNER TO snp;

--
-- Name: idlink; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE idlink (
    id integer NOT NULL,
    expid character varying(60) NOT NULL,
    patientid character varying(20) NOT NULL,
    studyid character varying(20) NOT NULL,
    sampleid character varying(20) NOT NULL
);


ALTER TABLE hapmap.idlink OWNER TO snp;

--
-- Name: idlink_id_seq; Type: SEQUENCE; Schema: hapmap; Owner: snp
--

CREATE SEQUENCE idlink_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE hapmap.idlink_id_seq OWNER TO snp;

--
-- Name: idlink_id_seq; Type: SEQUENCE OWNED BY; Schema: hapmap; Owner: snp
--

ALTER SEQUENCE idlink_id_seq OWNED BY idlink.id;


--
-- Name: pheno; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE pheno (
    patientid character varying(20) NOT NULL,
    sex_id character varying(1),
    race_id character varying(25),
    phenotype smallint
);


ALTER TABLE hapmap.pheno OWNER TO snp;

--
-- Name: race; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE race (
    val character varying(25) NOT NULL
);


ALTER TABLE hapmap.race OWNER TO snp;

--
-- Name: sex; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE sex (
    val character varying(1) NOT NULL,
    code integer NOT NULL,
    genabel_code integer NOT NULL
);


ALTER TABLE hapmap.sex OWNER TO snp;

--
-- Name: snpval; Type: TABLE; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE TABLE snpval (
    val integer NOT NULL
);


ALTER TABLE hapmap.snpval OWNER TO snp;

--
-- Name: snpval_val_seq; Type: SEQUENCE; Schema: hapmap; Owner: snp
--

CREATE SEQUENCE snpval_val_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE hapmap.snpval_val_seq OWNER TO snp;

--
-- Name: snpval_val_seq; Type: SEQUENCE OWNED BY; Schema: hapmap; Owner: snp
--

ALTER SEQUENCE snpval_val_seq OWNED BY snpval.val;


--
-- Name: id; Type: DEFAULT; Schema: hapmap; Owner: snp
--

ALTER TABLE anno ALTER COLUMN id SET DEFAULT nextval('anno_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: hapmap; Owner: snp
--

ALTER TABLE chromo ALTER COLUMN id SET DEFAULT nextval('chromo_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: hapmap; Owner: snp
--

ALTER TABLE idlink ALTER COLUMN id SET DEFAULT nextval('idlink_id_seq'::regclass);


--
-- Name: val; Type: DEFAULT; Schema: hapmap; Owner: snp
--

ALTER TABLE snpval ALTER COLUMN val SET DEFAULT nextval('snpval_val_seq'::regclass);


--
-- Name: allele_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY allele
    ADD CONSTRAINT allele_pkey PRIMARY KEY (val);


--
-- Name: anno_fid_key; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY anno
    ADD CONSTRAINT anno_fid_key UNIQUE (fid);


--
-- Name: anno_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY anno
    ADD CONSTRAINT anno_pkey PRIMARY KEY (id);


--
-- Name: chromo_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY chromo
    ADD CONSTRAINT chromo_pkey PRIMARY KEY (id);


--
-- Name: geno_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY geno
    ADD CONSTRAINT geno_pkey PRIMARY KEY (idlink_id, anno_id);


--
-- Name: idlink_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY idlink
    ADD CONSTRAINT idlink_pkey PRIMARY KEY (id);


--
-- Name: pheno_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY pheno
    ADD CONSTRAINT pheno_pkey PRIMARY KEY (patientid);


--
-- Name: race_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY race
    ADD CONSTRAINT race_pkey PRIMARY KEY (val);


--
-- Name: sex_code_key; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY sex
    ADD CONSTRAINT sex_code_key UNIQUE (code);


--
-- Name: sex_genabel_code_key; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY sex
    ADD CONSTRAINT sex_genabel_code_key UNIQUE (genabel_code);


--
-- Name: sex_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY sex
    ADD CONSTRAINT sex_pkey PRIMARY KEY (val);


--
-- Name: snpval_pkey; Type: CONSTRAINT; Schema: hapmap; Owner: snp; Tablespace: 
--

ALTER TABLE ONLY snpval
    ADD CONSTRAINT snpval_pkey PRIMARY KEY (val);


--
-- Name: ix_hapmap_anno_allelea_id; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_anno_allelea_id ON anno USING btree (allelea_id);


--
-- Name: ix_hapmap_anno_alleleb_id; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_anno_alleleb_id ON anno USING btree (alleleb_id);


--
-- Name: ix_hapmap_anno_chromosome; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_anno_chromosome ON anno USING btree (chromosome);


--
-- Name: ix_hapmap_anno_location; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_anno_location ON anno USING btree (location);


--
-- Name: ix_hapmap_anno_rsid; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_anno_rsid ON anno USING btree (rsid);


--
-- Name: ix_hapmap_chromo_name; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE UNIQUE INDEX ix_hapmap_chromo_name ON chromo USING btree (name);


--
-- Name: ix_hapmap_idlink_expid; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE UNIQUE INDEX ix_hapmap_idlink_expid ON idlink USING btree (expid);


--
-- Name: ix_hapmap_idlink_patientid; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_idlink_patientid ON idlink USING btree (patientid);


--
-- Name: ix_hapmap_idlink_sampleid; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_idlink_sampleid ON idlink USING btree (sampleid);


--
-- Name: ix_hapmap_idlink_studyid; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_idlink_studyid ON idlink USING btree (studyid);


--
-- Name: ix_hapmap_pheno_race_id; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_pheno_race_id ON pheno USING btree (race_id);


--
-- Name: ix_hapmap_pheno_sex_id; Type: INDEX; Schema: hapmap; Owner: snp; Tablespace: 
--

CREATE INDEX ix_hapmap_pheno_sex_id ON pheno USING btree (sex_id);


--
-- Name: anno_allelea_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY anno
    ADD CONSTRAINT anno_allelea_id_fkey FOREIGN KEY (allelea_id) REFERENCES allele(val) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: anno_alleleb_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY anno
    ADD CONSTRAINT anno_alleleb_id_fkey FOREIGN KEY (alleleb_id) REFERENCES allele(val) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: anno_chromosome_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY anno
    ADD CONSTRAINT anno_chromosome_fkey FOREIGN KEY (chromosome) REFERENCES chromo(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: geno_anno_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY geno
    ADD CONSTRAINT geno_anno_id_fkey FOREIGN KEY (anno_id) REFERENCES anno(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: geno_idlink_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY geno
    ADD CONSTRAINT geno_idlink_id_fkey FOREIGN KEY (idlink_id) REFERENCES idlink(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: geno_snpval_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY geno
    ADD CONSTRAINT geno_snpval_id_fkey FOREIGN KEY (snpval_id) REFERENCES snpval(val) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: idlink_patientid_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY idlink
    ADD CONSTRAINT idlink_patientid_fkey FOREIGN KEY (patientid) REFERENCES pheno(patientid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: pheno_race_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY pheno
    ADD CONSTRAINT pheno_race_id_fkey FOREIGN KEY (race_id) REFERENCES race(val) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: pheno_sex_id_fkey; Type: FK CONSTRAINT; Schema: hapmap; Owner: snp
--

ALTER TABLE ONLY pheno
    ADD CONSTRAINT pheno_sex_id_fkey FOREIGN KEY (sex_id) REFERENCES sex(val) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--s
\end{verbatim}

\item Size of database tables corresponding to the affy6 hapmap data set.
\begin{verbatim}
affy6_faheem=# select count(*) from anno;
 count
--------
 932979
(1 row)

affy6_faheem=# select count(*) from geno;
   count
-----------
 825733782
(1 row)
\end{verbatim}

\item List of space using entities in database over 1 MB.
\begin{verbatim}
affy6=# select relkind, relname, pg_catalog.pg_namespace.nspname, pg_size_pretty(pg_relation_size(pg_catalog.pg_class.oid))
from pg_catalog.pg_class inner join pg_catalog.pg_namespace on relnamespace=pg_catalog.pg_namespace.oid where nspname='hapmap' order by
pg_catalog.pg_namespace.oid, pg_relation_size(pg_catalog.pg_class.oid) desc;

 relkind |          relname           | nspname | pg_size_pretty
---------+----------------------------+---------+----------------
 r       | geno                       | hapmap  | 34 GB
 i       | geno_pkey                  | hapmap  | 17 GB
 r       | anno                       | hapmap  | 63 MB
 i       | ix_hapmap_anno_rsid        | hapmap  | 38 MB
 i       | anno_fid_key               | hapmap  | 37 MB
 i       | ix_hapmap_anno_location    | hapmap  | 26 MB
 i       | ix_hapmap_anno_chromosome  | hapmap  | 25 MB
 i       | ix_hapmap_anno_allelea_id  | hapmap  | 25 MB
 i       | ix_hapmap_anno_alleleb_id  | hapmap  | 25 MB
 i       | anno_pkey                  | hapmap  | 20 MB
\end{verbatim}
\end{itemize}

\end{document}

